（优化篇）MySQL 优化之索引

发表于 | 更新于 | 分类于 | 阅读次数：
本文字数： 9.1k | 阅读时长 ≈ 13 分钟

序言

  为了加快数据查询的速度，通常会使用到索引，那么什么是索引呢？它底层是如何构成的呢？又该如何正确使用呢？

什么是索引？

  MySQL 官方对索引的定义为：索引（Index）是帮助 MySQL 高效获取数据的数据结构。
  在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

  简单来讲，索引是数据结构。

  首先来看个没有索引的数据表：

  由于上表中并无索引，若我们想找到一个公司的数据行，需要检查数据表的每一行以确定其是否与期望值匹配，这将完全扫描此数据表。如果数据很大，但是仅有几个记录与搜索条件匹配，那么工作过程就很慢，效率就会很低。
  现将上表的company_num数据列添加索引，如下图：

  现在，索引包含了数据表中每一个数据行的项，且根据company_num的值来分类，因此，使用索引后，假设现要查找公司编号为 13 的所有数据行，则会开始扫描索引并将找到 3 个匹配的数据行。
  当继续扫描到公司编号为 14 的数据行（ 14 高于需要搜寻的值）时会发现：由于索引值是经过分类的，将知道不会再有与 13 相匹配的内容了，决定不再扫描。

  由此可知索引能提高查询效率的一个原因就是其可以得知匹配的数据行在什么位置结束，从而跳过其他部分。另一个原因则是定位算法的使用。

索引种类

按存储结构划分

  索引按存储结构可划分为：

  • 聚簇索引
  • 非聚簇索引

聚簇索引（聚集索引）

  聚簇索引中，聚簇指的是数据行和相邻的键值紧凑地存储在一起。

  因为无法把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。

  对聚簇索引来说，需要依赖一个唯一值确定，所以：

  • 若表已设主键，则主键就是聚簇索引
  • 若表未设主键，则会默认选择第一个唯一（UNIQUE）且NOT NULL的列作为聚簇索引
  • 若以上都没有，则会默认创建一个隐藏的row_id作为聚簇索引

  在 MySQL 中 InnoDB 数据引擎默认主索引就是聚集索引，聚集索引的索引结构中不仅包含了键值（一般为主键值），还相应地包含了表的数据。

  InnoDB 中必须要有一个聚集索引，而聚集索引在基于主键创建的，所以 InnoDB 的表必须要有主键，若没有显示指定主键，Mysql 会自动创建一个隐式主键且同时建立一个唯一的聚集索引。
  InnoDB 除了主索引（聚集索引）之外的索引都是以辅助索引（非聚簇索引）的形式存在，辅助索引每次检索的时候都先找到主索引再找到数据（回表）;

非聚簇索引（二级索引）

  与聚簇索引不同，非聚簇索引中的数据域不存储具体的数据，而是存储聚簇索引的主键值。

  与聚簇索引不同，一个表可以有不止一个非聚簇索引。

区别

  聚集索引与非聚集索引的区别是：叶子节点是否存放一整行记录

  • 对于聚簇索引表来说，表数据是和主键一起存储的，主键索引的叶子结点存储行数据（包含了主键值），即叶子节点上的数据是主键与具体记录（数据内容）
  • 对于非聚簇索引表来说，并没有存储表数据，存储的是聚簇索引的主键，数据需要去聚簇索引中查询

按数据结构划分

  索引按数据结构可划分为：

  • FULLTEXT 索引：是一种通过建立倒排索引，快速匹配文档的方式。类似于Lucene Solr，ES，常用于全文检索场景
  • SPATIAL 索引：MySQL 中只适用只适用 MyISAM
  • HASH 索引：底层数据结构是用哈希表实现的，只有精确匹配索引列的查询才有效，只能用于对等比较，不支持范围查询，无法排序数据，MySQL 中只适用 MEMORY引擎
  • R-tree索引：空间索引是 MyISAM 引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少
  • B+TREE索引：最常见的索引类型，大部分引擎都支持B+树索引，MySQL 默认使用

其他划分

唯一索引

  唯一索引限制了在表对应的唯一索引列上的值是唯一不可重复的，其具有如下特点：

  • 唯一唯一索引自然不能重复
  • 一个表可以创建多个唯一索引
  • 唯一索引的值允许为 NULL
  • 允许多个列建立唯一索引的组合（复合索引），这意味着那几个数据列的值的组合不能重复

注意哦：主键索引属于唯一索引的一个特殊种类，一个表的某列创建主键索引后会具备唯一索引的特点同时还会对该列生成主键约束，主键唯一且不允许有重复的值，并且不能为 NULL 值。

普通索引

  默认创建的索引就是普通索引，普通索引是非唯一的，所以允许索引项出现重复。

联合（复合）索引

  联合（复合）索引指的是对多个列创建一种多列的索引，这种索引可能是唯一索引，也可能是普通索引（非唯一索引）。

覆盖索引

  覆盖索引（Covering Index）是指一个索引包含了查询所需的所有数据，因此不需要访问表中的实际数据行。这样可以提高查询性能，因为数据库引擎只需读取索引即可获取所有需要的信息。

  例如，如果有一个表包含列 A、B 和 C，并且有一个索引覆盖了 A 和 B，那么对于只查询 A 和 B 的请求，数据库可以直接从索引中获取数据，而不需要访问表中的数据行。

  使用覆盖索引的好处包括：

  • 提高查询速度：减少了磁盘 I/O，因为只需访问索引。
  • 减少锁争用：因为不需要访问数据行，减少了锁的使用。
  • 降低存储成本：索引通常比表小，访问索引比访问表更快。

不同存储引擎的索引特性

  MySQL 提供了多种灵活的索引创建方法：

  • 可以为单个数据列创建索引，亦可以为多个数据列创建复合索引
  • 索引可以只包含独一无二的值，也可以包含重复的值
  • 可以为同一个数据表创建多个索引并分别利用它们来优化基于不同数据列的查询

  而不同的存储引擎对索引功能的支持情况存在如下差异：

特性 InnoDB MyISAM MEMOry MERGE NDB
存储限制 64 TB 有 有 无 有
锁机制 行锁（适合高并发） 表锁 表锁 表锁 行锁
BTREE 索引 支持 支持 支持 支持 支持
HASH 索引 支持
FULLTEXT 索引 支持（5.6 版本后） 支持
缓存 支持 支持 支持 支持 支持
数据可压缩 支持
空间使用 高 低 N/A 低 低
内存使用 高 低 中 低 高
批量插入速度 低 高 高 高 高

注意哦：其中只有 InnoDB 引擎支持事务和外键。

  对于不同的存储引擎，索引实现的细节不同：

  • InnoDB：将所有数据表的数据和索引存储在一个表空间中，亦可为每个数据表分别创建一个表空间
  • MyISAM：将数据表的数据行存储在数据文件中，索引值则在索引文件中

如何选择存储引擎？

  在选择存储引擎时，应该根据应用系统的特点选择合适的存储引擎：

  • InnoDB ：是 MySQL 默认的存储引擎，支持事务、外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询以外，还包括很多的更新、删除操作，那么 InnoDB 存储引擎时比较合适的选择。InnoDB 存储引擎除了有效的降低由于删除和更新导致的锁定，还可以确保食物的完整提交和回滚，对于类似于计费系统或者财务系统等对数据准确性要求比较高的系统，InnoDB 是最合适的选择。
  • MyISAM ：如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个引擎时非常合适的。
  • MEMORY ： 将所有的数据保存在内存中，在需要快速定位记录和其他类似数据环境下，可以提供极快的访问。MEMORY 的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，其次是要确保表的数据可以恢复，数据库异常终止后表中的数据是可以恢复的。MEMORY 表通常用于更新不太频繁的小表，用以快速得到访问结果。

注意哦:对于复杂的应用系统，还可以根据实际情况选择多种存储引擎进行组合

索引语法

创建索引

  创建索引有 2 种方式：

  • 在建表时创建索引：
    • CREATE INDEX 索引名 ON 表名(列名[前缀长度])
    • CREATE PRIMARY KEY 索引名 ON 表名(列名[前缀长度])
    • CREATE UNIQUE KEY 索引名 ON 表名(列名[前缀长度])
    • CREATE FULLTEXT 索引名 ON 表名(列名[前缀长度])
    • CREATE SPATIAL 索引名 ON 表名(列名[前缀长度])
    • CREATE HASH 索引名 ON 表名(列名[前缀长度])
  • 给现有表添加索引：
    • ALTER TABLE 表名 ADD INDEX 索引名(列名[前缀长度]，列名[前缀长度]...)
    • ALTER TABLE 表名 ADD PRIMARY KEY 索引名(列名[前缀长度])
    • ALTER TABLE 表名 ADD UNIQUE KEY 索引名(列名[前缀长度])
    • ALTER TABLE 表名 ADD FULLTEXT 索引名(列名[前缀长度])
    • ALTER TABLE 表名 ADD SPATIAL 索引名(列名[前缀长度])
    • ALTER TABLE 表名 ADD HASH 索引名(列名[前缀长度])

注意哦：CREATE 创建索引时索引名不可选且不能创建多个索引，而ALTER可以。其中[前缀长度]为可选参数。

删除索引

  删除索引也有 2 种方式：

  • DROP INDEX 索引名 ON 表名
  • ALTER TABLE 表名 DROP INDEX 索引名

索引优劣

优

  • 提高查询效率，降低数据库的 IO 成本，降低 CPU 的消耗

缺

  • 索引存在失效情况
  • 索引种类繁多，需要开发人员分析业务选择合适的索引
  • 索引会占据磁盘空间，多个索引则会占据更大的空间
  • 对带索引的数据列执行插入、删除及修改操作时执行速度会降低，因为这些操作时所有索引都得跟着改变

失效情况

  并不是每次查询都能通过索引走捷径，因为索引还存在以下失效情况：

  • 评估：若 MySQL 评估全表扫描比索引更快，则不使用索引
  • 联合索引：
    • 不遵循最左前缀法则时
    • 范围查询时右边的列不走索引
  • 特殊处理：
    • 字符串索引不加单引号时
    • 对索引隐式类型转换可能会失效
    • 在索引列上使用函数或表达式运算时
  • 关键字维度：
    • IN 可以走索引，NOT IN 不一定走索引
    • LIKE 模糊查询以 % 开头（可通过覆盖索引解决）
    • 用 OR 分割开的条件，若 OR 中的一列中有索引，一列中无索引

失效原因分析

为什么对索引隐式类型转换可能会失效

当涉及到隐式类型转换时，MySQL可能无法有效利用索引。这是因为在进行隐式类型转换时，查询优化器可能无法正确地将转换后的数据与索引中存储的值进行匹配，从而导致索引无法使用。换句话说，索引在某些情况下仅能对原始的数据类型有效，而在进行类型转换后，索引失效。

注意哦：在 MySQL 中，数字与字符串的比较经常会使用到索引，特别是当隐式类型转换的结果能匹配索引时。

为什么对索引使用函数不走索引

因为索引保存的是索引字段的原始值，而不是经过函数计算后的值，自然就没办法走索引了。

不过，从 MySQL 8.0 开始，索引特性增加了函数索引，即可以针对函数计算后的值建立一个索引，也就是说该索引的值是函数计算后的值，所以就可以通过扫描索引来查询数据。

为什么对索引使用表达式运算不走索引

在查询条件中对索引进行表达式计算，也是无法走索引的。

比如，下面这条查询语句，执行计划中 type = ALL，说明是通过全表扫描的方式查询数据的：

1
explain select * from t_user where id + 1 = 10;

但是，如果把查询语句的条件改成 where id = 10 - 1，这样就不是在索引字段进行表达式计算了，于是就可以走索引查询了。

为什么对索引进行表达式计算，就无法走索引了呢？

原因跟对索引使用函数差不多。

因为索引保存的是索引字段的原始值，而不是 id + 1 表达式计算后的值，所以无法走索引，只能通过把索引字段的取值都取出来，然后依次进行表达式的计算来进行条件判断，因此采用的就是全表扫描的方式。

有的同学可能会说，这种对索引进行简单的表达式计算，在代码特殊处理下，应该是可以做到索引扫描的，比方将 id + 1 = 10 变成 id = 10 - 1。

是的，是能够实现，但是 MySQL 还是偷了这个懒，没有实现。

我的想法是，可能也是因为，表达式计算的情况多种多样，每种都要考虑的话，代码可能会很臃肿，所以干脆将这种索引失效的场景告诉程序员，让程序员自己保证在查询条件中不要对索引进行表达式计算。

为什么 IN 可以走索引，NOT IN 不一定走索引

在 MySQL 中，IN 和 NOT IN 虽然语法上相似，但它们的执行计划和优化行为存在差异，特别是在与索引的配合上。IN 可以走索引，而 NOT IN 不一定走索引，原因主要和查询优化器如何选择执行计划有关。

IN 的分析

如果查询字段（比如 id）上有索引，MySQL 会利用索引来加速 IN 操作。因为索引提供了快速的查找能力，MySQL 可以高效地找到属于指定集合的值。

内部转换为 OR 条件

如果 IN 列表非常小且不包含大量的重复值，MySQL 可能会将其转换为多个 OR 条件，从而类似于手动写多个 = 条件来执行。特别是对于小范围的 IN 列表，优化器可能会认为通过多个简单的 OR 条件比较会更加高效。

从逻辑上来说，IN 可以看作是多个 OR 条件的组合，实际上就是多个 = 比较的简化版。比如：

1
SELECT * FROM products WHERE id IN (1, 2, 3);

等同于：

1
SELECT * FROM products WHERE id = 1 OR id = 2 OR id = 3;

在这个例子中，MySQL 会检查 id 是否等于 1、2 或 3，最终返回所有匹配的记录。这两者的逻辑等价，都表示一个集合中的值进行逐一匹配。

哈希查找优化

对于较大的 IN 列表（例如包含很多值），MySQL 可能会使用一种称为 “哈希查找” 的优化方式。具体地，MySQL 会将 IN 列表中的值转换为哈希表，然后使用该哈希表来加速查找过程。这样做的好处是可以避免逐个扫描所有值并与每一行进行匹配。

这种优化方式通常会在查询包含大量值的情况下生效：

1
SELECT * FROM products WHERE id IN (1, 2, 3, 4, 5, ..., 1000);

在这种情况下，MySQL 会将 IN 列表中的值生成一个哈希表，然后利用该哈希表快速查找对应的值。

注意使用场景：哈希查找优化通常适用于 IN 列表很大时，尤其在没有有效索引的情况下，MySQL 会倾向于使用这种方法。

大数据集时的性能问题

对于非常大的 IN 列表，虽然 MySQL 在底层做了优化，但查询仍然可能变得非常慢，尤其是在 IN 列表中有很多值时。此时，MySQL 可能仍然会选择全表扫描或进行多次索引查找，这会导致性能下降。

当然也有解决方法：

  • 分批查询：当 IN 列表中包含大量的值时，可以考虑将查询拆分成多个小批次查询，减少每个查询的工作量。
  • 优化索引：确保查询字段上有合适的索引，可以提高 IN 操作的查询效率。
  • 使用临时表：如果 IN 的值非常多，可以考虑将这些值放入一个临时表中，并通过连接 (JOIN) 来优化查询
NOT IN

NOT IN 查询比 IN 更复杂，因为它需要确保排除某些值，这涉及到对每个值的逐一排除和可能的多次扫描。在某些情况下，尤其是当排除的值列表很长时，MySQL 可能会选择使用全表扫描而不是使用索引，尤其是如果优化器认为全表扫描的成本较低时。

NOT IN 实质上是对等于条件（=）的否定，因此 MySQL 可能认为在某些情况下，使用索引来处理 NOT IN 查询可能不如全表扫描高效。例如，查询中可能涉及多个 NOT IN 条件，导致优化器无法有效地利用索引的过滤能力，进而选择全表扫描。

为什么 OR 存在索引列和非索引列不走索引

因为 OR 的含义就是两个只要满足一个即可，因此只有一个条件列是索引列是没有意义的，只要有条件列不是索引列，就会进行全表扫描。

如何挑选索引？

  我们在创建索引时该选择为哪些列添加呢？总结如下

  • 尽量为用作搜寻、分类或分组的数据列创建索引，不要为作为输出显示的数据列创建索引。换而言之，根据SELECT 输出的数据列最好不要创建索引，而适合索引的数据列包括：

    • 在WHERE子句出现的
    • 在连接（联结）子句给出的
    • ORDER BY或GROUP BY子句出现的
      1
      2
      3
      4
      5
      6
      7
      SELECT 
          col_a    // 最好不要创建索引
      FROM
          t1 LEFT JOIN t2
          ON b1.col_b = b2.col_c    // 适合创建索引
      WHERE 
          col_d = expr;    // 适合创建索引

  • 尽量选择非重复值多的列

  • 尽量选择数值更小的列
  • 选择最左边的前缀
  • 尽量使用复合索引，而少使用单列索引
1
2
3
4
5
6
7
8
9
10
11
-- 创建复合索引相当于创建了多个索引
CREATE INDEX idx_name_status_address ON t_user(name,status,address)
-- 上面相当于创建了 3 个索引
-- name
-- name + status
-- name + status + address

-- 而创建单列索引时数据库会选择一个最优的索引来使用，不会使用全部索引
CREATE INDEX idx_name ON t_user(name,status,address)
CREATE INDEX idx_status ON t_user(status)
CREATE INDEX idx_address ON t_user(address)

  这样总结可能有点混乱，其实也可以细分为：

  • 什么场景使用索引？
  • 什么场景不用索引？

什么场景使用索引？

  • 用于连接的列（如 LEFT JOIN … ON ... ）
  • 经常要用于查询的列（如 WHERE id = ?）
  • 经常要用于排序（ORDER BY）和分组（GROUP BY）的列，因为索引已经排好序了;
  • 具有值唯一性限制的列，比如说主键、用户名
  • 利用最左前缀，N 个列组合而成的复合索引就相当于参加了 N 个索引，如果查询时 WHERE 子句中使用了组成该索引的前几个字段，那么这条查询 SQL 可以利用组合索引来提升查询效率，如
    1
    2
    3
    4
    5
    6
    7
    -- 创建复合索引
    CREATE INDEX idx_name_email_status ON t_user(name,email,status)

    -- 相当于
       --   对 name 创建索引；
       --   对 name, email 创建索引；
       --   对 name, email , status 创建索引；

什么场景不用索引？

  • 不要给不经常使用的列建索引，不怎么查询还建索引干嘛;
  • 不要盲目的给表建太多索引，因为索引本身的存储也要占用存储空间，一旦更新操作频繁反而降低新性能;
  • 不要给 img、text 等数据类型使用索引，因为这种字段一般使用很少，且其数据量太大，非要使用建议使用 ES 做特殊处理;
  • 不要给高重复值的列建索引，索引本身就是为了提高查询速度，然而数据值高度重复，数据区别性不高，索引起不了效果）（如：性别）;
  • 数据更新性能比查询性能要求要高的情况下不要使用索引，因为数据的更新的同时索引也要进行维护和更新（加了索引查询快但更新就会慢）;

扩展

索引下推

  在MySQL中，索引下推（Index Condition Pushdown，简称ICP）是一个优化技术，它指的是将某些查询条件从查询的WHERE 子句推送到索引扫描阶段，从而减少不必要的行扫描，提高查询效率。

具体工作原理：

  1. 传统索引扫描：在执行一个查询时，MySQL会扫描索引，查找符合条件的索引项，然后回表（从数据表中读取完整的记录）来验证其他的查询条件。
  2. 索引下推：在启用了索引下推的情况下，MySQL会将某些查询条件（尤其是那些可以通过索引直接判断的条件）推送到索引扫描阶段，从而减少回表的次数。

示例

  假设有一个表 users，包含 id, name, 和 age 三个字段，且在 id 和 age 上建立了索引。

  如果我们执行以下查询：

1
2
3
SELECT id, name, age 
FROM users 
WHERE age > 30 AND id < 1000;

  在没有索引下推的情况下，MySQL首先会使用 id 索引查找符合 id < 1000 的记录，然后通过回表（读取完整的表行）来检查 age > 30 是否符合条件。

  如果启用了索引下推，MySQL会将 age > 30 这个条件下推到索引扫描阶段，直接在索引扫描时就过滤掉那些不符合 age > 30 条件的记录，从而避免了回表操作，提升了性能。

启用索引下推：

  从MySQL 5.6版本开始，Index Condition Pushdown 就作为一个可选的优化策略。一般情况下，MySQL会自动决定是否使用索引下推，但是你也可以通过以下方式来查看该特性是否启用：

1
SHOW VARIABLES LIKE 'optimizer_switch';

优点

  • 减少回表：通过将过滤条件推送到索引阶段，可以避免读取不必要的表数据，从而减少 I/O 开销
  • 提升查询性能：特别是在联合索引上，索引下推能够显著提升多条件查询的性能。

限制

  • 索引下推只适用于可通过索引字段快速判断的条件
  • 并不是所有类型的查询条件都适合下推。例如，复杂的计算或者子查询可能无法直接在索引层面进行推送。

Mysql 的 B+Tree 索引

  在 MySQL 中索引默认使用的数据结构是优化后的 B+Tree 。
  对原 B+Tree 而言，只有叶子节点保存着 key 信息，查询任何 key 都要从根节点走到叶子节点，与 BTree 相比其查询效率更加稳定。而 MySQL 在原 B+Tree 的基础上，增加了一个指向相邻叶子结点的链表指针，形成了带有顺序指针的 B+Tree，这提高了区间访问（范围查找）的性能。

疑问

为什么 lnnoDB 存储引擎选择使用 B+tree 的数据结构?

  • 相对于二叉树，层级更少，搜索效率高;
  • 相对于 B-tree，B-tree 无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低；
  • 相对 Hash 索引，B+tree 支持范围匹配及排序操作;

为什么不直接将数据行进行分类，从而省掉索引呢？

  数据表可能不止一个索引，如顾客表中在可以使用 ID 号作为索引时，还可以使用电话号作为索引，或者更多的索引。

参考

  • Paul DuBois. MySQL 技术内幕 [M]. 人民邮电出版社, 2011

文章信息

时间 说明
2019-10-22 初稿
2025-01-04 增加失效原因分析一节（待重构）
0%